﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace EnglishRemix.Services.Model
{
    public class DBUtility
    {
        public static void AddSqlParameter(ref SqlParameter[] sqlParameters, string parameterName, SqlDbType type, object value)
        {
            List<SqlParameter> sqlParameterList = new List<SqlParameter>();
            if (sqlParameters != null && sqlParameters.Length > 0)
            {
                sqlParameterList = sqlParameters.ToList();
            }

            SqlParameter param = new SqlParameter();
            param.ParameterName = parameterName;
            param.SqlDbType = type;
            if (type == SqlDbType.NVarChar)
            {
                value = Convert.ToString(value).Replace("'", "''");
            }
            param.Value = value;

            sqlParameterList.Add(param);
            sqlParameters = sqlParameterList.ToArray();
        }

        public static DataSet ExecuteDataset(string SqlStr, SqlParameter[] sqlParameters)
        {
            string SqlConStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
            return SqlHelper.ExecuteDataset(SqlConStr, CommandType.Text, SqlStr, sqlParameters);
        }

        public static void ExcuteNonQuery(string SqlStr, SqlParameter[] sqlParameters)
        {
            string SqlConStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
            SqlHelper.ExecuteNonQuery(SqlConStr, CommandType.Text, SqlStr, sqlParameters);
        }

        public static void ExcuteBatchNonQuery(List<string> sqlCommandList, List<SqlParameter[]> sqlParameterList)
        {
            if (sqlCommandList.Count <= 0 || sqlParameterList.Count <= 0 || sqlCommandList.Count != sqlParameterList.Count)
            {
                throw new ArgumentException("Argument sqlCommandList and sqlParameterList are needed Or the number of sqlCommandList and sqlParameterList are not matched.");
            }

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString);
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                for (int i = 0; i < sqlCommandList.Count; i++ )
                {
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlCommandList[i], sqlParameterList[i]);
                }
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new ApplicationException(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
